﻿Imports System.Data.SqlClient
Imports Futuros.model
Imports System.Data.OleDb

Namespace DAO
    Public Class DAOCostos
        Public Shared Function guardarCostos(ByVal custid As String, ByVal classid As String, ByVal fecha_op As String, _
                                             ByVal origen As String, ByVal pto_destino As String, ByVal costoContable As String, _
                                             ByVal motonave As String, ByVal qty_cierre As String, ByVal um_cierre As String, _
                                             ByVal precio_venta As String, ByVal id_cbot_futuro As String, _
                                             ByVal id_cbot_prima As String, ByVal perpost As String, ByVal holg_seguro As String, _
                                             ByVal tasa_seguro As String, ByVal dias_plazo_vta As String, ByVal overHead As String, _
                                             ByVal cant_contratos As String, ByVal monto_spred As String, _
                                             ByVal tasa_merma As String, ByVal tip_venta As String, ByVal tip_despacho As String, _
                                             ByVal futuro_compra As String, ByVal estado As String, _
                                             ByVal tip_pres As String, ByVal lsCostos As List(Of xm_cierre_det)) As String
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlTrans As OleDb.OleDbTransaction
            Dim sqlCmd1, sqlCmd2 As OleDb.OleDbCommand
            Dim objCierre_op As String
            conn = New Conexion()

            Try
                conn.Conexion.Open()
                sqlTrans = conn.Conexion.BeginTransaction(IsolationLevel.ReadCommitted)
                ' Obteniendo el siguiente id
                sqlCmd1 = New OleDb.OleDbCommand("select isnull(cast(DatePart(year, getdate()) as varchar(4)) + cast(DatePart(Month, getdate()) as varchar(2))+RIGHT('0000'+ CAST(max(right(cierre_op,4))+1 AS VARCHAR(4)), 4), cast(DatePart(year, getdate()) as varchar(4)) + cast(DatePart(Month, getdate()) as varchar(2))+ '0001') from xm_cierre_enc where SUBSTRING ( cierre_op , 1 , 6 ) = cast(DatePart(year, getdate()) as varchar(4)) + cast(DatePart(Month, getdate()) as varchar(2))", conn.Conexion, sqlTrans)
                sqlDr = sqlCmd1.ExecuteReader
                Do While sqlDr.Read()
                    objCierre_op = sqlDr.Item(0)
                Loop

                ' Guardo los datos en la tabla xm_cierre_enc

                sqlCmd2 = New OleDb.OleDbCommand("insert into xm_cierre_enc (cierre_op,custid,classid,fecha_op,origen,pto_destino," + _
                                                 "motonave,perpost,holg_seguro,tasa_seguro,dias_plazo_vta,overHead," + _
                                                 "tasa_merma,tasa_interes,qty_cierre,um_cierre,precio_venta," + _
                                                 "id_cbot_futuro,id_cbot_prima,cant_contratos,monto_spread,tip_venta,tip_despacho,futuro_compra,estado,tip_presen) " + _
                                                 "values (?,?,?,convert(smalldatetime,convert(char(10),?,101),101),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", conn.Conexion, sqlTrans)
                sqlCmd2.Parameters.Add(New OleDbParameter("cierre_op", objCierre_op))
                sqlCmd2.Parameters.Add(New OleDbParameter("custid", custid))
                sqlCmd2.Parameters.Add(New OleDbParameter("classid", classid))
                sqlCmd2.Parameters.Add(New OleDbParameter("fecha_op", fecha_op))
                sqlCmd2.Parameters.Add(New OleDbParameter("origen", origen))
                sqlCmd2.Parameters.Add(New OleDbParameter("pto_destino", pto_destino))
                sqlCmd2.Parameters.Add(New OleDbParameter("motonave", motonave))
                sqlCmd2.Parameters.Add(New OleDbParameter("perpost", perpost))
                sqlCmd2.Parameters.Add(New OleDbParameter("holg_seguro", holg_seguro))
                sqlCmd2.Parameters.Add(New OleDbParameter("tasa_seguro", tasa_seguro))
                sqlCmd2.Parameters.Add(New OleDbParameter("dias_plazo_vta", dias_plazo_vta))
                sqlCmd2.Parameters.Add(New OleDbParameter("overHead", overHead))
                sqlCmd2.Parameters.Add(New OleDbParameter("tasa_merma", tasa_merma))
                sqlCmd2.Parameters.Add(New OleDbParameter("tasa_interes", costoContable))
                sqlCmd2.Parameters.Add(New OleDbParameter("qty_cierre", qty_cierre))
                sqlCmd2.Parameters.Add(New OleDbParameter("um_cierre", um_cierre))
                sqlCmd2.Parameters.Add(New OleDbParameter("precio_venta", precio_venta))
                sqlCmd2.Parameters.Add(New OleDbParameter("id_cbot_futuro", id_cbot_futuro))
                sqlCmd2.Parameters.Add(New OleDbParameter("id_cbot_prima", id_cbot_prima))
                sqlCmd2.Parameters.Add(New OleDbParameter("cant_contratos", cant_contratos))
                sqlCmd2.Parameters.Add(New OleDbParameter("monto_spred", monto_spred))
                sqlCmd2.Parameters.Add(New OleDbParameter("tip_venta", tip_venta))
                sqlCmd2.Parameters.Add(New OleDbParameter("tip_despacho", tip_despacho))
                sqlCmd2.Parameters.Add(New OleDbParameter("futuro_compra", futuro_compra))
                sqlCmd2.Parameters.Add(New OleDbParameter("estado", estado))
                sqlCmd2.Parameters.Add(New OleDbParameter("tip_presen", tip_pres))
                sqlCmd2.ExecuteNonQuery()

                ' guardo el detalle de los datos en la tabla xm_cierre_det

                For Each c As xm_cierre_det In lsCostos
                    sqlCmd2 = New OleDb.OleDbCommand("insert into xm_cierre_det (cierre_op,classid,costid,sequence,total_cost) " + _
                                     "values (?,?,?,?,?)", conn.Conexion, sqlTrans)

                    sqlCmd2.Parameters.Add(New OleDbParameter("cierre_op", objCierre_op))
                    sqlCmd2.Parameters.Add(New OleDbParameter("classid", classid)) ' toDo Colocar clase
                    sqlCmd2.Parameters.Add(New OleDbParameter("costid", c.Costid))
                    sqlCmd2.Parameters.Add(New OleDbParameter("sequence", c.Sequence))
                    sqlCmd2.Parameters.Add(New OleDbParameter("total_cost", c.Total_cost))
                    sqlCmd2.ExecuteNonQuery()
                Next

                sqlTrans.Commit()
                MessageBox.Show("Se guardaron los costos de manera exitosa.", "Guardar Costos", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Return objCierre_op
            Catch ex As Exception
                MsgBox(ex.ToString)
                sqlTrans.Rollback()
                MessageBox.Show("Ocurrio un error al guardar los datos:" + ex.ToString, "Guardar Costos", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Return ""
            End Try
        End Function
        Public Shared Sub guardarCostos(ByVal cierreop As String, ByVal custid As String, ByVal classid As String, _
                                        ByVal fecha_op As String, ByVal origen As String, ByVal pto_destino As String, _
                                        ByVal costoContable As String, ByVal motonave As String, ByVal qty_cierre As String, _
                                        ByVal um_cierre As String, ByVal precio_venta As String, ByVal id_cbot_futuro As String, _
                                        ByVal id_cbot_prima As String, ByVal perpost As String, ByVal holg_seguro As String, _
                                        ByVal tasa_seguro As String, ByVal dias_plazo_vta As String, ByVal overHead As String, _
                                        ByVal cant_contratos As String, ByVal monto_spred As String, _
                                        ByVal tasa_merma As String, ByVal tip_venta As String, ByVal tip_despacho As String, _
                                        ByVal futuro_compra As String, ByVal estado As String, ByVal tip_pres As String, _
                                        ByVal lsCostos As List(Of xm_cierre_det))
            Dim conn As Conexion
            Dim sqlTrans As OleDb.OleDbTransaction
            Dim sqlCmd2 As OleDb.OleDbCommand
            conn = New Conexion()

            Try
                conn.Conexion.Open()
                sqlTrans = conn.Conexion.BeginTransaction(IsolationLevel.ReadCommitted)
                ' Obteniendo el siguiente id

                ' Actualizo los datos en la tabla xm_cierre_enc

                sqlCmd2 = New OleDb.OleDbCommand("UPDATE xm_cierre_enc SET custid=?, classid=?, " + _
                                                 "fecha_op=convert(smalldatetime,convert(char(10),?,101),101)," + _
                                                 " origen=?, pto_destino=?, motonave=?, perpost=?, holg_seguro=?," + _
                                                 " tasa_seguro=?, dias_plazo_vta=?, overHead=?, tasa_merma=?," + _
                                                 " tasa_interes=?, qty_cierre=?, um_cierre=?, precio_venta=?," + _
                                                 " id_cbot_futuro=?, id_cbot_prima=?, cant_contratos=?, " + _
                                                 " monto_spread=?, tip_venta = ?, tip_despacho = ?, " + _
                                                 " futuro_compra=?, estado = ?, tip_presen = ?  WHERE cierre_op = ?", conn.Conexion, sqlTrans)

                sqlCmd2.Parameters.Add(New OleDbParameter("custid", custid))
                sqlCmd2.Parameters.Add(New OleDbParameter("classid", classid))
                sqlCmd2.Parameters.Add(New OleDbParameter("fecha_op", fecha_op))
                sqlCmd2.Parameters.Add(New OleDbParameter("origen", origen))
                sqlCmd2.Parameters.Add(New OleDbParameter("pto_destino", pto_destino))
                sqlCmd2.Parameters.Add(New OleDbParameter("motonave", motonave))
                sqlCmd2.Parameters.Add(New OleDbParameter("perpost", perpost))
                sqlCmd2.Parameters.Add(New OleDbParameter("holg_seguro", holg_seguro))
                sqlCmd2.Parameters.Add(New OleDbParameter("tasa_seguro", tasa_seguro))
                sqlCmd2.Parameters.Add(New OleDbParameter("dias_plazo_vta", dias_plazo_vta))
                sqlCmd2.Parameters.Add(New OleDbParameter("overHead", overHead))
                sqlCmd2.Parameters.Add(New OleDbParameter("tasa_merma", tasa_merma))
                sqlCmd2.Parameters.Add(New OleDbParameter("tasa_interes", costoContable))
                sqlCmd2.Parameters.Add(New OleDbParameter("qty_cierre", qty_cierre))
                sqlCmd2.Parameters.Add(New OleDbParameter("um_cierre", um_cierre))
                sqlCmd2.Parameters.Add(New OleDbParameter("precio_venta", precio_venta))
                sqlCmd2.Parameters.Add(New OleDbParameter("id_cbot_futuro", id_cbot_futuro))
                sqlCmd2.Parameters.Add(New OleDbParameter("id_cbot_prima", id_cbot_prima))
                sqlCmd2.Parameters.Add(New OleDbParameter("cant_contratos", cant_contratos))
                sqlCmd2.Parameters.Add(New OleDbParameter("monto_spred", monto_spred))

                sqlCmd2.Parameters.Add(New OleDbParameter("tip_venta", tip_venta))
                sqlCmd2.Parameters.Add(New OleDbParameter("tip_despacho", tip_despacho))
                sqlCmd2.Parameters.Add(New OleDbParameter("futuro_compra", futuro_compra))
                sqlCmd2.Parameters.Add(New OleDbParameter("estado", estado))
                sqlCmd2.Parameters.Add(New OleDbParameter("tip_presen", tip_pres))

                sqlCmd2.Parameters.Add(New OleDbParameter("cierre_op", cierreop))

                sqlCmd2.ExecuteNonQuery()

                ' Actualizo el detalle de los datos en la tabla xm_cierre_det

                For Each c As xm_cierre_det In lsCostos
                    sqlCmd2 = New OleDb.OleDbCommand("UPDATE xm_cierre_det SET  classid= ?, sequence= ? , total_cost= ? WHERE cierre_op= ? and  costid= ?", conn.Conexion, sqlTrans)

                    sqlCmd2.Parameters.Add(New OleDbParameter("classid", classid))
                    sqlCmd2.Parameters.Add(New OleDbParameter("sequence", c.Sequence))
                    sqlCmd2.Parameters.Add(New OleDbParameter("total_cost", c.Total_cost))
                    sqlCmd2.Parameters.Add(New OleDbParameter("cierre_op", cierreop))
                    sqlCmd2.Parameters.Add(New OleDbParameter("costid", c.Costid))

                    sqlCmd2.ExecuteNonQuery()
                Next

                sqlTrans.Commit()
                MessageBox.Show("Se actualizaron los costos de manera exitosa.", "Actualizar Costos", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Catch ex As Exception
                MessageBox.Show("Ocurrio un error al actualizar los costos:" + ex.ToString, "Actualizar Costos", MessageBoxButtons.OK, MessageBoxIcon.Information)
                sqlTrans.Rollback()

            End Try
        End Sub

        Public Shared Function cargarCostoEnc(ByVal cierreop As String) As xm_cierre_enc
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlCmd As OleDb.OleDbCommand
            Dim objCierreEnc As xm_cierre_enc = New xm_cierre_enc
            conn = New Conexion()
            Try
                conn.Conexion.Open()
                sqlCmd = New OleDb.OleDbCommand("select * from xm_cierre_enc where cierre_op = ?", conn.Conexion)
                sqlCmd.Parameters.Add(New OleDbParameter("cierre_op", cierreop))
                sqlDr = sqlCmd.ExecuteReader
                If sqlDr.HasRows Then
                    Do While sqlDr.Read()
                        objCierreEnc.Cierre_op = sqlDr.Item("cierre_op")
                        objCierreEnc.Custid = sqlDr.Item("custid")
                        objCierreEnc.Classid = sqlDr.Item("classid")
                        objCierreEnc.Fecha_op = sqlDr.Item("fecha_op")
                        objCierreEnc.Origen = sqlDr.Item("origen")
                        objCierreEnc.Pto_destino = sqlDr.Item("pto_destino")
                        objCierreEnc.Motonave = sqlDr.Item("motonave")
                        objCierreEnc.Perpost = sqlDr.Item("perpost")
                        objCierreEnc.Holg_seguro = sqlDr.Item("holg_seguro")
                        objCierreEnc.Tasa_seguro = sqlDr.Item("tasa_seguro")
                        objCierreEnc.Dias_plazo_vta = sqlDr.Item("dias_plazo_vta")
                        objCierreEnc.OverHead = sqlDr.Item("overHead")
                        objCierreEnc.Tasa_merma = sqlDr.Item("tasa_merma")
                        objCierreEnc.Tasa_Interes = sqlDr.Item("tasa_interes")
                        objCierreEnc.Qty_cierre = sqlDr.Item("qty_cierre")
                        objCierreEnc.Um_cierre = sqlDr.Item("um_cierre")
                        objCierreEnc.Precio_venta = sqlDr.Item("precio_venta")
                        objCierreEnc.Id_cbot_futuro = sqlDr.Item("id_cbot_futuro")
                        objCierreEnc.Id_cbot_prima = sqlDr.Item("id_cbot_prima")
                        objCierreEnc.Cant_contratos = sqlDr.Item("cant_contratos")
                        objCierreEnc.Monto_spread = sqlDr.Item("monto_spread")
                        objCierreEnc.Tip_venta = sqlDr.Item("tip_venta")
                        objCierreEnc.Tip_despacho = sqlDr.Item("tip_despacho")
                        objCierreEnc.Futuro_compra = sqlDr.Item("futuro_compra")
                        objCierreEnc.Tip_presen = sqlDr.Item("tip_presen")
                        objCierreEnc.Estado = sqlDr.Item("estado")

                    Loop
                End If
                conn.Conexion.Close()
            Catch ex As Exception
                conn.Conexion.Close()
                Return objCierreEnc
            End Try
            Return objCierreEnc
        End Function
        Public Shared Function cargarCostoDet(ByVal cierreop As String) As List(Of xm_cost_item)
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlCmd As OleDb.OleDbCommand
            Dim lsCostoDet As List(Of xm_cost_item) = New List(Of xm_cost_item)
            conn = New Conexion()
            Try
                conn.Conexion.Open()
                sqlCmd = New OleDb.OleDbCommand("select xm_cierre_det.costid, xm_cost_item.descr, xm_cierre_det.total_cost, xm_cost_item.grupo, xm_cierre_det.sequence, xm_cost_item.tipo from xm_cierre_det " + _
                                                "inner join xm_cost_item on xm_cierre_det.costid = xm_cost_item.costid where xm_cierre_det.cierre_op = ? " + _
                                                "order by xm_cierre_det.sequence ", conn.Conexion)
                sqlCmd.Parameters.Add(New OleDbParameter("cierre_op", cierreop))
                sqlDr = sqlCmd.ExecuteReader
                If sqlDr.HasRows Then
                    Do While sqlDr.Read()
                        Dim objCostItem As xm_cost_item = New xm_cost_item
                        objCostItem.Costid = sqlDr.Item(0)
                        objCostItem.Descr = sqlDr.Item(1)
                        objCostItem.Total_cost = sqlDr.Item(2)
                        objCostItem.Grupo = sqlDr.Item(3)
                        objCostItem.Prioridad = sqlDr.Item(4)
                        objCostItem.Tipo = sqlDr.Item(5)
                        lsCostoDet.Add(objCostItem)
                    Loop
                End If
                conn.Conexion.Close()
            Catch ex As Exception
                MsgBox(ex.ToString)
                conn.Conexion.Close()
                Return lsCostoDet
            End Try
            Return lsCostoDet
        End Function
        Public Shared Function obtenerPerpost() As List(Of String)
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlCmd As OleDb.OleDbCommand
            Dim lsPerpost As List(Of String) = New List(Of String)

            conn = New Conexion()
            Try
                conn.Conexion.Open()
                sqlCmd = New OleDb.OleDbCommand("select perpost from xm_cierre_enc", conn.Conexion)
                sqlDr = sqlCmd.ExecuteReader
                If sqlDr.HasRows Then
                    Do While sqlDr.Read()
                        Dim obj As String
                        obj = sqlDr.Item(0)
                        lsPerpost.Add(obj)
                    Loop
                End If
                conn.Conexion.Close()
            Catch ex As Exception
                conn.Conexion.Close()
                Return lsPerpost
            End Try
            Return lsPerpost
        End Function

        Public Shared Function obtenerParametros() As xm_config_per
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlCmd As OleDb.OleDbCommand
            Dim objParams As xm_config_per = New xm_config_per
            conn = New Conexion()
            Try
                conn.Conexion.Open()
                sqlCmd = New OleDb.OleDbCommand("select top 1 * from xm_config_per inner join arsetup on xm_config_per.perpost = arsetup.pernbr order by perpost desc", conn.Conexion)
                sqlDr = sqlCmd.ExecuteReader
                If sqlDr.HasRows Then
                    Do While sqlDr.Read()
                        objParams.Perpost = sqlDr.Item("perpost")
                        objParams.Holg_seguro = sqlDr.Item("holg_seguro")
                        objParams.Tasa_seguro = sqlDr.Item("tasa_seguro")
                        objParams.Dias_plazo_vta = sqlDr.Item("dias_plazo_vta")
                        objParams.Overhead = sqlDr.Item("overHead")
                        objParams.Tasa_merma = sqlDr.Item("tasa_merma")
                        objParams.Tasa_interes = sqlDr.Item("tasa_interes")
                    Loop
                End If
                conn.Conexion.Close()
            Catch ex As Exception
                conn.Conexion.Close()
                Return objParams
            End Try
            Return objParams
        End Function
        Public Shared Function obtenerCBOTProducto(ByVal classid As String) As xm_cbot_prod
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlCmd As OleDb.OleDbCommand
            Dim objCbotProd As xm_cbot_prod = New xm_cbot_prod

            conn = New Conexion()
            Try
                conn.Conexion.Open()
                sqlCmd = New OleDb.OleDbCommand("select * from xm_cbot_prod where classid = ?", conn.Conexion)
                sqlCmd.Parameters.Add(New OleDbParameter("classid", classid))
                sqlDr = sqlCmd.ExecuteReader
                If sqlDr.HasRows Then
                    Do While sqlDr.Read()
                        objCbotProd.Id_prod = sqlDr.Item("id_prod")
                        objCbotProd.Name = sqlDr.Item("name")
                        objCbotProd.classid = sqlDr.Item("classid")
                        objCbotProd.Simb_Cbot = sqlDr.Item("simb_Cbot")
                        objCbotProd.UC = sqlDr.Item("UC")
                        objCbotProd.Fact_UC_USD = sqlDr.Item("fact_UC_USD")
                        objCbotProd.Fact_contrato = sqlDr.Item("fact_contrato")
                    Loop
                End If
                conn.Conexion.Close()
            Catch ex As Exception
                conn.Conexion.Close()
                Return objCbotProd
            End Try
            Return objCbotProd
        End Function
        Public Shared Function obtenercBOTMesById(ByVal id_cbot_futuro As String) As Object
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlCmd As OleDb.OleDbCommand
            Dim objCbotFut As xm_cbot_mes = New xm_cbot_mes
            conn = New Conexion()
            Try
                conn.Conexion.Open()
                sqlCmd = New OleDb.OleDbCommand("select * from xm_cbot_mes where id_cbot = ?", conn.Conexion)
                sqlCmd.Parameters.Add(New OleDbParameter("id_cbot", id_cbot_futuro))
                sqlDr = sqlCmd.ExecuteReader
                If sqlDr.HasRows Then
                    Do While sqlDr.Read()
                        objCbotFut.Id_prod = sqlDr.Item("id_prod")
                        objCbotFut.Id_cbot = sqlDr.Item("id_cbot")
                        objCbotFut.Nomenclatura = sqlDr.Item("nomenclatura")
                        objCbotFut.Anno = sqlDr.Item("anno")
                        objCbotFut.Mes = sqlDr.Item("mes")
                        objCbotFut.Text1_cbot = sqlDr.Item("text1_cbot")
                        objCbotFut.Text2_cbot = sqlDr.Item("text2_cbot")
                        objCbotFut.Estado = sqlDr.Item("estado")
                    Loop
                End If
                conn.Conexion.Close()
            Catch ex As Exception
                conn.Conexion.Close()
                Return objCbotFut
            End Try
            Return objCbotFut
        End Function
        Public Shared Function obtenercBOTMes(ByVal classid As String) As List(Of xm_cbot_mes)
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlCmd As OleDb.OleDbCommand
            Dim lsCBOTFuturo As List(Of xm_cbot_mes) = New List(Of xm_cbot_mes)

            conn = New Conexion()
            Try
                conn.Conexion.Open()
                sqlCmd = New OleDb.OleDbCommand("select * from xm_cbot_mes where id_prod = ?", conn.Conexion)
                sqlCmd.Parameters.Add(New OleDbParameter("classid", classid))
                sqlDr = sqlCmd.ExecuteReader
                If sqlDr.HasRows Then
                    Do While sqlDr.Read()
                        Dim objCbotFut As xm_cbot_mes = New xm_cbot_mes
                        objCbotFut.Id_prod = sqlDr.Item("id_prod")
                        objCbotFut.Id_cbot = sqlDr.Item("id_cbot")
                        objCbotFut.Nomenclatura = sqlDr.Item("nomenclatura")
                        objCbotFut.Anno = sqlDr.Item("anno")
                        objCbotFut.Mes = sqlDr.Item("mes")
                        objCbotFut.Text1_cbot = sqlDr.Item("text1_cbot")
                        objCbotFut.Text2_cbot = sqlDr.Item("text2_cbot")
                        objCbotFut.Estado = sqlDr.Item("estado")
                        lsCBOTFuturo.Add(objCbotFut)
                    Loop
                End If
                conn.Conexion.Close()
            Catch ex As Exception
                conn.Conexion.Close()
                Return lsCBOTFuturo
            End Try
            Return lsCBOTFuturo


        End Function
        Public Shared Function obtenerCierres() As List(Of String)
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlCmd As OleDb.OleDbCommand
            Dim lsCierres As List(Of String) = New List(Of String)

            conn = New Conexion()
            Try
                conn.Conexion.Open()
                sqlCmd = New OleDb.OleDbCommand("select cierre_op from xm_cierre_enc", conn.Conexion)
                sqlDr = sqlCmd.ExecuteReader
                If sqlDr.HasRows Then
                    Do While sqlDr.Read()
                        Dim obj As String
                        obj = sqlDr.Item(0)
                        lsCierres.Add(obj)
                    Loop
                End If
                conn.Conexion.Close()
            Catch ex As Exception
                conn.Conexion.Close()
                Return lsCierres
            End Try
            Return lsCierres
        End Function
        Public Shared Function obtenerCostos() As List(Of xm_cost_item)
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlCmd As OleDb.OleDbCommand
            Dim lsCostos As List(Of xm_cost_item) = New List(Of xm_cost_item)

            conn = New Conexion()
            Try
                conn.Conexion.Open()
                sqlCmd = New OleDb.OleDbCommand("select * from xm_cost_item order by prioridad", conn.Conexion)
                sqlDr = sqlCmd.ExecuteReader
                If sqlDr.HasRows Then
                    Do While sqlDr.Read()
                        Dim obj As xm_cost_item = New xm_cost_item
                        obj.Costid = sqlDr.Item("costid")
                        obj.Descr = sqlDr.Item("descr")
                        obj.Tipo = sqlDr.Item("tipo")
                        obj.Prioridad = sqlDr.Item("prioridad")
                        obj.Grupo = sqlDr.Item("grupo")
                        lsCostos.Add(obj)
                    Loop
                End If
                conn.Conexion.Close()
            Catch ex As Exception
                conn.Conexion.Close()
                Return lsCostos
            End Try
            Return lsCostos

        End Function
        Public Shared Function obtenerCostosFijos(ByVal puerto As String, ByVal origen As String, ByVal classid As String, ByVal perpost As String) As List(Of xm_cierre_det)
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlCmd As OleDb.OleDbCommand
            Dim lsCostos As List(Of xm_cierre_det) = New List(Of xm_cierre_det)

            conn = New Conexion()
            Try
                conn.Conexion.Open()
                sqlCmd = New OleDb.OleDbCommand("select costid, " + puerto.Trim + "_cost from xm_cfg_cost_item where classid = ? and perpost = ?", conn.Conexion)
                sqlCmd.Parameters.Add(New OleDbParameter("classid", classid))
                sqlCmd.Parameters.Add(New OleDbParameter("perpost", perpost))
                sqlDr = sqlCmd.ExecuteReader
                If sqlDr.HasRows Then
                    Do While sqlDr.Read()
                        Dim obj As xm_cierre_det = New xm_cierre_det
                        obj.Costid = sqlDr.Item("costid")
                        obj.Total_cost = sqlDr.Item(1)
                        lsCostos.Add(obj)
                    Loop
                End If
                conn.Conexion.Close()
            Catch ex As Exception
                conn.Conexion.Close()
                Return lsCostos
            End Try
            Return lsCostos
        End Function
        Public Shared Function obtenerClientes() As List(Of String)
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlCmd As OleDb.OleDbCommand
            Dim lsClientes As List(Of String) = New List(Of String)

            conn = New Conexion()
            Try
                conn.Conexion.Open()
                sqlCmd = New OleDb.OleDbCommand("select name from customer", conn.Conexion)
                sqlDr = sqlCmd.ExecuteReader
                If sqlDr.HasRows Then
                    Do While sqlDr.Read()
                        Dim obj As String
                        obj = sqlDr.Item(0)
                        lsClientes.Add(obj)
                    Loop
                End If
                conn.Conexion.Close()
            Catch ex As Exception
                conn.Conexion.Close()
                Return lsClientes
            End Try
            Return lsClientes
        End Function
        Public Shared Function obtenerTipoDespacho() As List(Of tipodespacho)
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlCmd As OleDb.OleDbCommand
            Dim lsTipoDespachos As List(Of tipodespacho) = New List(Of tipodespacho)

            conn = New Conexion()
            Try
                conn.Conexion.Open()
                sqlCmd = New OleDb.OleDbCommand("select shipViaID,descr,aplica_flete = case when DfltFrtMthd = 'F'" + _
                                                " then 1 else 0 end from shipVia", conn.Conexion)
                sqlDr = sqlCmd.ExecuteReader
                If sqlDr.HasRows Then
                    Do While sqlDr.Read()
                        Dim objTipoDespacho As tipodespacho = New tipodespacho
                        objTipoDespacho.ShipViaId = sqlDr.Item("shipViaID")
                        objTipoDespacho.Desc = sqlDr.Item("descr")
                        objTipoDespacho.Aplica_flete = sqlDr.Item("aplica_flete")
                        lsTipoDespachos.Add(objTipoDespacho)
                    Loop
                End If
                conn.Conexion.Close()
            Catch ex As Exception
                conn.Conexion.Close()
                Return lsTipoDespachos
            End Try
            Return lsTipoDespachos
        End Function
        Public Shared Function obtenerPresentaciones() As List(Of segdef)
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlCmd As OleDb.OleDbCommand
            Dim lsPresentaciones As List(Of segdef) = New List(Of segdef)

            conn = New Conexion()
            Try
                conn.Conexion.Open()
                sqlCmd = New OleDb.OleDbCommand("select id, description from segdef as segdef where " + _
                                                " segdef.fieldclassname = 'INVENTORYITEM' and segdef.segnumber = 4 and id in " + _
                                                " ('01','02','03') order by id ", conn.Conexion)
                sqlDr = sqlCmd.ExecuteReader
                If sqlDr.HasRows Then
                    Do While sqlDr.Read()
                        Dim objSegdef As segdef = New segdef
                        objSegdef.Id = sqlDr.Item("id")
                        objSegdef.Description = sqlDr.Item("description")
                        lsPresentaciones.Add(objSegdef)
                    Loop
                End If
                conn.Conexion.Close()
            Catch ex As Exception
                conn.Conexion.Close()
                Return lsPresentaciones
            End Try
            Return lsPresentaciones
        End Function
        Public Shared Function obtenerProductos() As List(Of String)
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlCmd As OleDb.OleDbCommand
            Dim lsProductos As List(Of String) = New List(Of String)

            conn = New Conexion()
            Try
                conn.Conexion.Open()
                sqlCmd = New OleDb.OleDbCommand("select descr from inventory", conn.Conexion)
                sqlDr = sqlCmd.ExecuteReader
                If sqlDr.HasRows Then
                    Do While sqlDr.Read()
                        Dim obj As String
                        obj = sqlDr.Item(0)
                        lsProductos.Add(obj)
                    Loop
                End If
                conn.Conexion.Close()
            Catch ex As Exception
                conn.Conexion.Close()
                Return lsProductos
            End Try
            Return lsProductos
        End Function
        Public Shared Function obtenerTipoProductos() As List(Of String)
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlCmd As OleDb.OleDbCommand
            Dim lsProductos As List(Of String) = New List(Of String)

            conn = New Conexion()
            Try
                conn.Conexion.Open()
                sqlCmd = New OleDb.OleDbCommand("select classid from productclass", conn.Conexion)
                sqlDr = sqlCmd.ExecuteReader
                If sqlDr.HasRows Then
                    Do While sqlDr.Read()
                        Dim obj As String
                        obj = sqlDr.Item(0)
                        lsProductos.Add(obj)
                    Loop
                End If
                conn.Conexion.Close()
            Catch ex As Exception
                conn.Conexion.Close()
                Return lsProductos
            End Try
            Return lsProductos
        End Function
        Public Shared Function obtenerUmByClassId(ByVal classid As String) As String
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlCmd As OleDb.OleDbCommand
            Dim strUm As String = ""
            conn = New Conexion()
            Try
                conn.Conexion.Open()
                sqlCmd = New OleDb.OleDbCommand("select dfltSOUnit from productclass where classid = ?", conn.Conexion)
                sqlCmd.Parameters.Add(New OleDbParameter("classid", classid))
                sqlDr = sqlCmd.ExecuteReader
                If sqlDr.HasRows Then
                    Do While sqlDr.Read()
                        strUm = sqlDr.Item(0)
                    Loop
                End If
                conn.Conexion.Close()
            Catch ex As Exception
                conn.Conexion.Close()
                Return strUm
            End Try
            Return strUm
        End Function

        Public Shared Function obtenerOrigen() As List(Of String)
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlCmd As OleDb.OleDbCommand
            Dim lsOrigen As List(Of String) = New List(Of String)

            conn = New Conexion()
            Try
                conn.Conexion.Open()
                sqlCmd = New OleDb.OleDbCommand("select nompais from cipaises", conn.Conexion)
                sqlDr = sqlCmd.ExecuteReader
                If sqlDr.HasRows Then
                    Do While sqlDr.Read()
                        Dim obj As String
                        obj = sqlDr.Item(0)
                        lsOrigen.Add(obj)
                    Loop
                End If
                conn.Conexion.Close()
            Catch ex As Exception
                conn.Conexion.Close()
                Return lsOrigen
            End Try
            Return lsOrigen
        End Function
        Public Shared Function obtenerPuertos() As List(Of String)
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlCmd As OleDb.OleDbCommand
            Dim lsPuertos As List(Of String) = New List(Of String)

            conn = New Conexion()
            Try
                conn.Conexion.Open()
                sqlCmd = New OleDb.OleDbCommand("select nomPto from cipuertos where paispto = 'PE' ", conn.Conexion)
                sqlDr = sqlCmd.ExecuteReader
                If sqlDr.HasRows Then
                    Do While sqlDr.Read()
                        Dim obj As String
                        obj = sqlDr.Item(0)
                        lsPuertos.Add(obj)
                    Loop
                End If
                conn.Conexion.Close()
            Catch ex As Exception
                conn.Conexion.Close()
                Return lsPuertos
            End Try
            Return lsPuertos
        End Function
        Public Shared Function obtenerMotonaves() As List(Of segdef)
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlCmd As OleDb.OleDbCommand
            Dim lsMotoNaves As List(Of segdef) = New List(Of segdef)

            conn = New Conexion()
            Try
                conn.Conexion.Open()
                sqlCmd = New OleDb.OleDbCommand("select id, description from segdef as segdef where segdef.fieldclassname = 'SUBACCOUNT' and segdef.segnumber = 2 order by description", conn.Conexion)
                sqlDr = sqlCmd.ExecuteReader
                If sqlDr.HasRows Then
                    Do While sqlDr.Read()
                        Dim objSegdef As segdef = New segdef
                        objSegdef.Id = sqlDr.Item("id")
                        objSegdef.Description = sqlDr.Item("description")
                        lsMotoNaves.Add(objSegdef)
                    Loop
                End If
                conn.Conexion.Close()
            Catch ex As Exception
                conn.Conexion.Close()
                Return lsMotoNaves
            End Try
            Return lsMotoNaves
        End Function
        Public Shared Function obtenerMotonavePorId(ByVal id As String) As segdef
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlCmd As OleDb.OleDbCommand
            Dim objsegdef As segdef = New segdef

            conn = New Conexion()
            Try
                conn.Conexion.Open()
                sqlCmd = New OleDb.OleDbCommand("select top 1 id, description from segdef where id =? ", conn.Conexion)
                sqlCmd.Parameters.Add(New OleDbParameter("id", id))
                sqlDr = sqlCmd.ExecuteReader
                If sqlDr.HasRows Then
                    Do While sqlDr.Read()
                        objSegdef.Id = sqlDr.Item("id")
                        objSegdef.Description = sqlDr.Item("description")
                    Loop
                End If
                conn.Conexion.Close()
            Catch ex As Exception
                conn.Conexion.Close()
                Return objsegdef
            End Try
            Return objsegdef

        End Function

        Public Shared Function obtenerUMs() As List(Of String)
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlCmd As OleDb.OleDbCommand
            Dim lsUM As List(Of String) = New List(Of String)

            conn = New Conexion()
            Try
                conn.Conexion.Open()
                sqlCmd = New OleDb.OleDbCommand("select distinct(tounit) from inunit", conn.Conexion)
                sqlDr = sqlCmd.ExecuteReader
                If sqlDr.HasRows Then
                    Do While sqlDr.Read()
                        Dim obj As String
                        obj = sqlDr.Item(0)
                        lsUM.Add(obj)
                    Loop
                End If
                conn.Conexion.Close()
            Catch ex As Exception
                conn.Conexion.Close()
                Return lsUM
            End Try
            Return lsUM
        End Function
        Public Shared Function obtenerCodCliente(ByVal customer As String) As String
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlCmd As OleDb.OleDbCommand
            Dim strCustid As String = ""

            conn = New Conexion()
            Try
                conn.Conexion.Open()
                sqlCmd = New OleDb.OleDbCommand("select top 1 custid from customer where name = ? ", conn.Conexion)
                sqlCmd.Parameters.Add(New OleDbParameter("name", customer))
                sqlDr = sqlCmd.ExecuteReader
                If sqlDr.HasRows Then
                    Do While sqlDr.Read()
                        strCustid = sqlDr.Item(0)
                    Loop
                End If
                conn.Conexion.Close()
            Catch ex As Exception
                conn.Conexion.Close()
                Return strCustid
            End Try
            Return strCustid
        End Function

        Public Shared Function obtenerCodOrigen(ByVal origen As String) As String
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlCmd As OleDb.OleDbCommand
            Dim strCodPais As String = ""

            conn = New Conexion()
            Try
                conn.Conexion.Open()
                sqlCmd = New OleDb.OleDbCommand("select codpais from cipaises where nompais = ? ", conn.Conexion)
                sqlCmd.Parameters.Add(New OleDbParameter("nompais", origen))
                sqlDr = sqlCmd.ExecuteReader
                If sqlDr.HasRows Then
                    Do While sqlDr.Read()
                        strCodPais = sqlDr.Item(0)
                    Loop
                End If
                conn.Conexion.Close()
            Catch ex As Exception
                conn.Conexion.Close()
                Return strCodPais
            End Try
            Return strCodPais
        End Function

        Public Shared Function obtenerCodPuerto(ByVal puerto As String) As String
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlCmd As OleDb.OleDbCommand
            Dim strCodPuerto As String = ""

            conn = New Conexion()
            Try
                conn.Conexion.Open()
                sqlCmd = New OleDb.OleDbCommand("select codpto from cipuertos where nompto = ? ", conn.Conexion)
                sqlCmd.Parameters.Add(New OleDbParameter("nompuerto", puerto))
                sqlDr = sqlCmd.ExecuteReader
                If sqlDr.HasRows Then
                    Do While sqlDr.Read()
                        strCodPuerto = sqlDr.Item(0)
                    Loop
                End If
                conn.Conexion.Close()
            Catch ex As Exception
                conn.Conexion.Close()
                Return strCodPuerto
            End Try
            Return strCodPuerto
        End Function
        Public Shared Function obtenerNombreCliente(ByVal custid As String) As String
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlCmd As OleDb.OleDbCommand
            Dim strCustName As String = ""

            conn = New Conexion()
            Try
                conn.Conexion.Open()
                sqlCmd = New OleDb.OleDbCommand("select top 1 name from customer where custid = ? ", conn.Conexion)
                sqlCmd.Parameters.Add(New OleDbParameter("custid", custid))
                sqlDr = sqlCmd.ExecuteReader
                If sqlDr.HasRows Then
                    Do While sqlDr.Read()
                        strCustName = sqlDr.Item(0)
                    Loop
                End If
                conn.Conexion.Close()
            Catch ex As Exception
                conn.Conexion.Close()
                Return strCustName
            End Try
            Return strCustName
        End Function
        Public Shared Function obtenerNombreOrigen(ByVal codpais As String) As String
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlCmd As OleDb.OleDbCommand
            Dim strNomPais As String = ""

            conn = New Conexion()
            Try
                conn.Conexion.Open()
                sqlCmd = New OleDb.OleDbCommand("select nompais from cipaises where codpais= ? ", conn.Conexion)
                sqlCmd.Parameters.Add(New OleDbParameter("codpais", codpais))
                sqlDr = sqlCmd.ExecuteReader
                If sqlDr.HasRows Then
                    Do While sqlDr.Read()
                        strNomPais = sqlDr.Item(0)
                    Loop
                End If
                conn.Conexion.Close()
            Catch ex As Exception
                conn.Conexion.Close()
                Return strNomPais
            End Try
            Return strNomPais
        End Function
        Public Shared Function obtenerNombrePuerto(ByVal codpto As String) As String
            Dim sqlDr As OleDbDataReader
            Dim conn As Conexion
            Dim sqlCmd As OleDb.OleDbCommand
            Dim strNomPuerto As String = ""

            conn = New Conexion()
            Try
                conn.Conexion.Open()
                sqlCmd = New OleDb.OleDbCommand("select nompto from cipuertos where codpto= ? ", conn.Conexion)
                sqlCmd.Parameters.Add(New OleDbParameter("codpto", codpto))
                sqlDr = sqlCmd.ExecuteReader
                If sqlDr.HasRows Then
                    Do While sqlDr.Read()
                        strNomPuerto = sqlDr.Item(0)
                    Loop
                End If
                conn.Conexion.Close()
            Catch ex As Exception
                conn.Conexion.Close()
                Return strNomPuerto
            End Try
            Return strNomPuerto
        End Function

    End Class
End Namespace

